using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using DTO;

namespace DAOLayer
{
    public class SachDao
    {
        public static IList LayDanhSach()
        {
            ArrayList ds = new ArrayList();
            OleDbConnection cn;
            string strSQL;

            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            strSQL = "Select * From Sach";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();

            SachDto sach = new SachDto();
            while (dr.Read())
            {
                sach = new SachDto();
                sach.MASACH = (int)dr["MASACH"];
                sach.TENSACH = (string)dr["TENSACH"];
                sach.MATACGIA = (int)dr["MATACGIA"];
                sach.NAMXUATBAN = (int)dr["NAMXUATBAN"];
                sach.GIA = (int)dr["GIA"];
                sach.TINHTRANG = (bool)dr["TINHTRANG"];
                sach.MALOAISACH = (int)dr["MALOAISACH"];
                
                ds.Add(sach);
            }
            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return ds;
        }

        public static DataTable LayBang()
        {
            DataTable dt = new DataTable();
            
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From Sach";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            
            // B5: Dong ket noi CSDL
            cn.Close();
            return dt;
        }
        public static void Them(SachDto sach)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Insert into Sach(Tensach,Maloaisach,Matacgia,Namxuatban,Tinhtrang,Gia) values (?,?,?,?,?,?)" +
            "";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@Tensach", OleDbType.WChar);
            cmd.Parameters.Add("@Maloaisach", OleDbType.Integer);
            cmd.Parameters.Add("@Matacgia", OleDbType.Integer);
            cmd.Parameters.Add("@Namxuatban", OleDbType.Integer);
            cmd.Parameters.Add("@Tinhtrang", OleDbType.Boolean);
            cmd.Parameters.Add("@Gia", OleDbType.Integer);

            cmd.Parameters["@Tensach"].Value = sach.TENSACH;
            cmd.Parameters["@Maloaisach"].Value = sach.MALOAISACH;
            cmd.Parameters["@Matacgia"].Value = sach.MATACGIA;
            cmd.Parameters["@Namxuatban"].Value = sach.NAMXUATBAN;
            cmd.Parameters["@Tinhtrang"].Value = sach.TINHTRANG;
            cmd.Parameters["@Gia"].Value = sach.GIA;

            cmd.ExecuteNonQuery();
            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            sach.MASACH = (int)cmd.ExecuteScalar();
            
            // B4: Dong ket noi CSDL
            cn.Close();
        }
        public static SachDto TimKiem(int ma)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
           
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From Sach Where Masach = ?";
            
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Masach", OleDbType.Integer);
            cmd.Parameters[0].Value = ma;
            OleDbDataReader dr = cmd.ExecuteReader();

            SachDto sach = null;
            while (dr.Read())
            {
                sach.MASACH = (int)dr["Masach"];
                sach.MALOAISACH = (int)dr["Maloaisach"];
                sach.MATACGIA = (int)dr["Matacgia"];
                sach.NAMXUATBAN = (int)dr["Namxuatban"];
                sach.TENSACH = (string)dr["Tensach"];
                sach.TINHTRANG=(bool)dr["Tinhtrang"];
                sach.GIA = (int)dr["Gia"];
            }
            //B5: Dong ket noi CSDL
            cn.Close();
            return sach;
        }
        public static void Xoa(int ma)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Delete From Sach Where Masach = ? ";

            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Masach", OleDbType.Integer);
            cmd.Parameters["@Masach"].Value = ma;
            cmd.ExecuteNonQuery();

            // B4: Dong ket noi CSDL
            cn.Close();
        }

        public static void Sua(SachDto sachDto)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Update Sach Set Tensach = ?, Maloaisach = ? , Matacgia = ? , Namxuatban = ? , Tinhtrang = ? , Gia = ? Where Masach = ?";

            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Tensach", OleDbType.WChar);
            cmd.Parameters.Add("@Maloaisach", OleDbType.Integer);
            cmd.Parameters.Add("@Matacgia", OleDbType.Integer);
            cmd.Parameters.Add("@Namxuatban", OleDbType.Integer);
            cmd.Parameters.Add("@Tinhtrang", OleDbType.Boolean);
            cmd.Parameters.Add("@Gia", OleDbType.Integer);
            cmd.Parameters.Add("@Masach", OleDbType.Integer);

            cmd.Parameters["@Tensach"].Value = sachDto.TENSACH;
            cmd.Parameters["@Maloaisach"].Value = sachDto.MALOAISACH;
            cmd.Parameters["@Matacgia"].Value = sachDto.MATACGIA;
            cmd.Parameters["@Namxuatban"].Value = sachDto.NAMXUATBAN;
            cmd.Parameters["@Tinhtrang"].Value = sachDto.TINHTRANG;
            cmd.Parameters["@Gia"].Value = sachDto.GIA;
            cmd.Parameters["@Masach"].Value = sachDto.MASACH;

            cmd.ExecuteNonQuery();
            // B4: Dong ket noi CSDL
            cn.Close();
        }

        public DataTable LayNXB()
        {
            OleDbConnection cn = DataProvider.ConnectionData();
            string strSQL = "select distinct NamXuatBan from Sach";
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);

            da.Fill(dt);
            cn.Close();
            return dt;
        }
        public DataTable LayGia()
        {
            OleDbConnection cn = DataProvider.ConnectionData();
            string strSQL = "select distinct Gia from Sach";
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);

            da.Fill(dt);
            cn.Close();
            return dt;
        }
        public DataTable TimKiem(SachCrt sCrt)
        {
            DataTable dt = new DataTable();
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            OleDbCommand cmd = BuildQuery(sCrt, cn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }
        private OleDbCommand BuildQuery(SachCrt sCrt, OleDbConnection cn)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strMaSach = "";
            string strTenSach = "";
            string strLoaiSach = "";
            string strTacgia = "";
            string strNXB = "";
            string strGia = "";
            string strTinhTrang = "";

            if(sCrt.Check_Masach)
            {
                strMaSach = " and s.Masach = ? ";
                cmd.Parameters.Add("@Masach", OleDbType.Integer);
                cmd.Parameters["@Masach"].Value = sCrt.MASACH;
            }
            if(sCrt.Check_LoaiSach == true)
            {
                strLoaiSach = " and s.Maloaisach = ? ";
                cmd.Parameters.Add("@Maloaisach", OleDbType.Integer);
                cmd.Parameters["@Maloaisach"].Value = sCrt.MALOAISACH;
            }
            if(sCrt.Check_TenSach == true)
            {
                strTenSach = " and s.Tensach LIKE ? ";
                cmd.Parameters.Add("@Tensach", OleDbType.WChar);
                cmd.Parameters["@Tensach"].Value = ("%" + sCrt.TENSACH + "%");
            }
            if(sCrt.Check_TacGia)
            {
                strTacgia = " and s.Matacgia = ? ";
                cmd.Parameters.Add("@Matacgia", OleDbType.Integer);
                cmd.Parameters["@Matacgia"].Value = sCrt.MATACGIA;
            }
            if(sCrt.Check_NXB)
            {
                strNXB = " and s.Namxuatban = ? ";
                cmd.Parameters.Add("@Namxuatban", OleDbType.Integer);
                cmd.Parameters["@Namxuatban"].Value = sCrt.NAMXUATBAN;
            }
            if(sCrt.Check_Gia)
            {
                strGia = " and s.Gia between ? and ? ";
                cmd.Parameters.Add("@Giaduoi", OleDbType.Integer);
                cmd.Parameters.Add("@Giatren", OleDbType.Integer);
                cmd.Parameters["@Giaduoi"].Value = sCrt.GIADUOI;
                cmd.Parameters["@Giatren"].Value = sCrt.GIATREN;
            }
            if(sCrt.Check_TinhTrang)
            {
                strTinhTrang = " and s.Tinhtrang = ? ";
                cmd.Parameters.Add("@Tinhtrang", OleDbType.Boolean);
                cmd.Parameters["@Tinhtrang"].Value = sCrt.TINHTRANG;
            }
            string strSQL;
            strSQL = "Select s.Masach,s.Tensach, ls.Loaisach,tg.Tentacgia,s.Namxuatban,s.Tinhtrang, s.Gia from Tacgia tg, Sach s, Loaisach ls " +
                " where tg.Matacgia = s.Matacgia and s.Maloaisach = ls.Maloaisach ";
            strSQL = strSQL + strMaSach + strLoaiSach + strTenSach + strTacgia + strNXB + strGia + strTinhTrang;

            cmd.Connection = cn;
            cmd.CommandText = strSQL;
            return cmd;
        }
  

        //ham tim kiem sach tong quat
        public IList TimKiemSach(string  sql)
        {
            ArrayList ds = new ArrayList();
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
             
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();

            SachDto sach = new SachDto();
            while (dr.Read())
            {
                sach = new SachDto();
                sach.MASACH = (int)dr["MASACH"];
                sach.TENSACH = (string)dr["TENSACH"];
                sach.MATACGIA = (int)dr["MATACGIA"];
                sach.NAMXUATBAN = (int)dr["NAMXUATBAN"];
                sach.GIA = (int)dr["GIA"];
                sach.TINHTRANG = (bool)dr["TINHTRANG"];
                sach.MALOAISACH = (int)dr["MALOAISACH"];

                ds.Add(sach);
            }
            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return ds;
        }
    }
}
